﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.ServiceModel;
using LocalServerApp.RemoteServerService;

namespace LocalServerApp
{
    public class DAO : IDao
    {
        private static DAO daoInstance;
        private string source = string.Format("Data Source={0}\\SQLEXPRESS;initial catalog={1};user id={2};password={3}",
                                                   Environment.MachineName,
                                                   "localServerDB",
                                                   "AppAdmin",
                                                   "HuUpdSC57lK"
                                                    );
        private DAO()
        {
        }

        public static DAO getInstance()
        {
            if (daoInstance == null)
            {
                daoInstance = new DAO();
            }
            return daoInstance;
        }


        public List<Hanger> getForgottenClothes()
        {
            List<Hanger> hangers = new List<Hanger>();

            SqlConnection conn = null;
            SqlCommand cmd = null;

            try
            {
                string q = "exec procForgottenClothes";
                conn = new SqlConnection(source);
                cmd = new SqlCommand(q, conn);

                conn.Open();

                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Hanger h = new Hanger();
                    Person p = new Person();

                    h.HangerNo = reader.GetInt32(0);

                    p.HashIdentifier = reader.GetString(1);
                    p.Name = reader.GetString(2);
                    p.Adress = reader.GetString(3);
                    p.Zipcode = reader.GetString(4);

                    if (reader.GetString(5).Equals("M"))
                    {
                        p.Male = true;
                    }
                    else if (reader.GetString(5).Equals("F"))
                    {
                        p.Male = false;
                    }
                    else
                    {
                        p.Male = null;
                    }

                    p.Birthday = reader.GetString(6);

                    h.Person = p;
                    hangers.Add(h);

                }
                reader.Close();
            }

            catch (System.Exception ex)
            {
                throw ex;
            }

            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }

                if (conn != null)
                {
                    conn.Dispose();
                }
            }

            return hangers;
        }

        public void endSession()
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;

            try
            {
                string q = "exec procEndSession";
                conn = new SqlConnection(source);
                cmd = new SqlCommand(q, conn);

                conn.Open();

                cmd.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }

                if (conn != null)
                {
                    conn.Dispose();
                }
            }
        }

        public void startSession()
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;

            try
            {
                string q = "exec procStartSession";
                conn = new SqlConnection(source);
                cmd = new SqlCommand(q, conn);

                conn.Open();

                cmd.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }

                if (conn != null)
                {
                    conn.Dispose();
                }
            }
        }

        public bool isInWardRobe(string cpr)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;

            try
            {
                conn = new SqlConnection(source);
                string q = "select count(*) from tblWardrobe where cpr='" + cpr + "'";
                cmd = new SqlCommand(q, conn);

                conn.Open();

                int count = (int)cmd.ExecuteScalar();

                if (count > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }

                if (conn != null)
                {
                    conn.Dispose();
                }
            }
        }

        public int depositClothes(string name, string adress, string zipcode, string cpr, bool? male, string birthdate)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;

            try
            {
                conn = new SqlConnection(source);

                string strMale = "U";
                if (male == true)
                {
                    strMale = "M";
                }
                else if (male == false)
                {
                    strMale = "F";
                }

                string q = "exec procDepositClothes @name, @adress, @zip, @cpr, " + strMale + ", @birthdate";
                cmd = new SqlCommand(q, conn);

                if (name == null)
                {
                    name = "";
                }
                cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = name;
                if (adress == null)
                {
                    adress = "";
                }
                cmd.Parameters.Add("@adress", SqlDbType.VarChar).Value = adress;
                if (zipcode == null)
                {
                    zipcode = "";
                }
                cmd.Parameters.Add("@zip", SqlDbType.VarChar).Value = zipcode;
                cmd.Parameters.Add("@cpr", SqlDbType.VarChar).Value = cpr;
                //cmd.Parameters.Add("@male", SqlDbType.VarChar).Value = male;
                if (birthdate == null)
                {
                    birthdate = "";
                }
                cmd.Parameters.Add("@birthdate", SqlDbType.VarChar).Value = birthdate;

                conn.Open();

                return (int)cmd.ExecuteScalar();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }

                if (conn != null)
                {
                    conn.Dispose();
                }
            }
        }

        public List<int> getClothesFromPerson(string hashIdentifier)
        {
            List<int> ints = new List<int>();
            SqlConnection conn = null;
            SqlCommand cmd = null;

            try
            {
                conn = new SqlConnection(source);
                conn.Open();
                string sql = "exec procGetClothesFromPerson @cpr";
                cmd = new SqlCommand(sql, conn);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("@cpr", SqlDbType.VarChar).Value = hashIdentifier;
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    ints.Add(reader.GetInt32(0));
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }

                if (conn != null)
                {
                    conn.Dispose();
                }
            }
            return ints;
        }

        public void withdrawClothes(int hangerNo)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;

            try
            {
                conn = new SqlConnection(source);
                string q = "exec procWithdrawClothes @hangerNo";
                cmd = new SqlCommand(q, conn);
                cmd.Parameters.Add("@hangerNo", SqlDbType.Int).Value = hangerNo;
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }

                if (conn != null)
                {
                    conn.Dispose();
                }
            }
        }

        public void cancelDepositClothes(int hangerNo)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;

            try
            {
                conn = new SqlConnection(source);
                string q = "exec procWithdrawClothes @hangerNo";
                cmd = new SqlCommand(q, conn);
                cmd.Parameters.Add("@hangerNo", SqlDbType.Int).Value = hangerNo;
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }

                if (conn != null)
                {
                    conn.Dispose();
                }
            }
        }

        public List<Person> searchPersonsInWardrobe(string name, string CPR, string adress, string zipcode, string hangerNo)
        {
            List<Person> lstPersons = new List<Person>();
            SqlConnection conn = null;
            SqlCommand cmd = null;

            try
            {
                conn = new SqlConnection(source);
                conn.Open();

                StringBuilder sql = new StringBuilder();
                sql.Append(@"
                    select	DISTINCT 
                            tblPerson.name, 
                            tblPerson.cpr, 
                            tblPerson.adress, 
                            tblPerson.zipcode, 
                            tblPerson.gender, 
                            tblPerson.birthdate
                    from	tblWardrobe, 
		                    tblPerson
                    where	tblWardrobe.cpr LIKE tblPerson.cpr");
                if ((name.Trim()).Length > 0)
                {
                    sql.Append(" AND tblPerson.name LIKE @name");
                }
                if ((adress.Trim()).Length > 0)
                {
                    sql.Append(" AND tblPerson.adress LIKE @adress");
                }
                if ((zipcode.Trim()).Length > 0)
                {
                    sql.Append(" AND tblPerson.zipcode LIKE @zipcode");
                }
                if ((hangerNo.Trim()).Length > 0)
                {
                    sql.Append(" AND tblWardrobe.hangerNo LIKE @hangerNo");
                }
                if ((CPR.Trim()).Length > 0)
                {
                    sql.Append(" AND tblWardrobe.CPR LIKE @CPR");
                }
                string sql2 = sql.ToString();
                cmd = new SqlCommand(sql2, conn);
                cmd.CommandType = CommandType.Text;
                if ((name.Trim()).Length > 0)
                {
                    cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = "%" + name + "%";
                }
                if ((adress.Trim()).Length > 0)
                {
                    cmd.Parameters.Add("@adress", SqlDbType.VarChar).Value = "%" + adress + "%";
                }
                if ((zipcode.Trim()).Length > 0)
                {
                    cmd.Parameters.Add("@zipcode", SqlDbType.VarChar).Value = "%" + zipcode + "%";
                }
                if ((hangerNo.Trim()).Length > 0)
                {
                    int i = -1;
                    try
                    {
                        i = int.Parse(hangerNo);
                    }
                    catch { }
                    cmd.Parameters.Add("@hangerNo", SqlDbType.Int).Value = i;
                }
                if ((CPR.Trim()).Length > 0)
                {
                    cmd.Parameters.Add("@CPR", SqlDbType.VarChar).Value = CPR;
                }

                SqlDataReader reader = cmd.ExecuteReader();
                Person p;
                while (reader.Read())
                {
                    p = new Person();
                    p.Name = reader.GetString(0);
                    p.HashIdentifier = reader.GetString(1);
                    p.Adress = reader.GetString(2);
                    p.Zipcode = reader.GetString(3);
                    string gender = reader.GetString(4);
                    if (gender.Equals("M"))
                        p.Male = true;
                    else if (gender.Equals("F"))
                        p.Male = false;
                    else
                        p.Male = null;
                    p.Birthday = reader.GetString(5);
                    lstPersons.Add(p);
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }

                if (conn != null)
                {
                    conn.Dispose();
                }
            }
            return lstPersons;
        }

        public string getCustomerID()
        {
            String ID = null;
            SqlConnection conn = null;
            SqlCommand cmd = null;

            try
            {
                conn = new SqlConnection(source);
                conn.Open();
                string sql = "exec procGetCustomerID";
                cmd = new SqlCommand(sql, conn);
                cmd.CommandType = CommandType.Text;
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    ID = reader.GetString(0);
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }

                if (conn != null)
                {
                    conn.Dispose();
                }
            }
            return ID;
        }

        public void setLastRemoteConnectionDate(DateTime date)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;

            try
            {
                conn = new SqlConnection(source);
                conn.Open();
                string sql = "exec procLastRemoteconnection @lastConnection";
                cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add("@lastConnection", SqlDbType.DateTime).Value = date;
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }

                if (conn != null)
                {
                    conn.Dispose();
                }
            }
        }

        public DateTime getLastRemoteConnection()
        {
            DateTime dt = new DateTime();
            SqlConnection conn = null;
            SqlCommand cmd = null;

            try
            {
                conn = new SqlConnection(source);
                conn.Open();
                string sql = "exec procGetLastRemoteConnection";
                cmd = new SqlCommand(sql, conn);
                cmd.CommandType = CommandType.Text;
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    dt = reader.GetDateTime(0);
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }

                if (conn != null)
                {
                    conn.Dispose();
                }
            }
            return dt;
        }

        public int getNumberOfHangers()
        {
            int numbers = 0;
            SqlConnection conn = null;
            SqlCommand cmd = null;

            try
            {
                conn = new SqlConnection(source);
                conn.Open();
                string sql = "exec procGetNumberOfHangers";
                cmd = new SqlCommand(sql, conn);
                cmd.CommandType = CommandType.Text;
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    numbers = reader.GetInt32(0);
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }

                if (conn != null)
                {
                    conn.Dispose();
                }
            }
            return numbers;
        }

        public void setNumberOfHangers(int numberOfHangers)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;

            try
            {
                conn = new SqlConnection(source);
                conn.Open();
                string sql = "exec procSetNumberOfHangers @NumberOfHangers";
                cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add("@numberOfHangers", SqlDbType.Int).Value = numberOfHangers;
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }

                if (conn != null)
                {
                    conn.Dispose();
                }
            }
        }


        public Dictionary<int, Person> GetDepositedClothes()
        {
            Dictionary<int, Person> result = new Dictionary<int, Person>();
            SqlConnection conn = null;
            SqlCommand cmd = null;

            try
            {
                conn = new SqlConnection(source);
                conn.Open();
                string sql = "exec procGetDepositedClothes";
                cmd = new SqlCommand(sql, conn);
                cmd.CommandType = CommandType.Text;
                SqlDataReader reader = cmd.ExecuteReader();
                Person pid;
                while (reader.Read())
                {
                    pid = new Person();
                    int key = reader.GetInt32(0);
                    pid.Name = reader.GetString(1);
                    pid.Adress = reader.GetString(2);
                    pid.Zipcode = reader.GetString(3);
                    pid.Birthday = reader.GetString(4);
                    result.Add(key, pid);
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }

                if (conn != null)
                {
                    conn.Dispose();
                }
            }
            return result;
        }

        // Method returning List of entries in the log combined with some person data
        public List<LogEntry> getLogEntries()
        {
            List<LogEntry> logEntries = new List<LogEntry>();
            SqlConnection conn = null;
            SqlCommand cmd = null;

            try
            {
                conn = new SqlConnection(source);
                conn.Open();
                string customerID = getCustomerID();
                string sql = "exec procGetLogEntries";
                cmd = new SqlCommand(sql, conn);
                cmd.CommandType = CommandType.Text;
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    LogEntry entry = new LogEntry();
                    entry.Cpr = reader.GetString(0);
                    entry.EventDsc = reader.GetString(1);
                    entry.Session = reader.GetDateTime(2);
                    entry.TimeStamp = reader.GetDateTime(3);
                    string x = reader.GetString(4);
                    if (x == null)
                        entry.Gender = null;
                    else if (x.Trim() == "U")
                        entry.Gender = null;
                    else if (x.Trim() == "M")
                        entry.Gender = true;
                    else entry.Gender = false;
                    entry.ZipCode = reader.GetString(5);
                    entry.Birthdate = reader.GetString(6);
                    entry.CustomerID = customerID;
                    logEntries.Add(entry);
                }
                reader.Close();
                return logEntries;
            }

            catch (Exception)
            {
                return logEntries;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }

                if (conn != null)
                {
                    conn.Dispose();
                }
            }
        }


        // Method deleting all entries in tbllog and tblPerson
        public void cleanLogs()
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;

            try
            {
                conn = new SqlConnection(source);
                conn.Open();
                string sql = "exec procCleanLogs";
                cmd = new SqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }

                if (conn != null)
                {
                    conn.Dispose();
                }
            }
        }
    }
}
